﻿using System;
using System.Collections.Generic;
using MySql.Data;
using MySql.Data.MySqlClient;

public class MysqlConnector
{
    private MySqlConnection connection;
    private string server;
    private string database;
    private string uid;
    private string password;
    private static MysqlConnector instance = null;
    private string connectionString;

    private MysqlConnector()
    {
        initialize();
    }

    public static MysqlConnector getInstance()
    {
        if (instance == null)
        {
            instance = new MysqlConnector();
        }

        return instance;
    }

    private void initialize()
    {
        server              = "thefilberts.dk";
        database            = "tasktracker";
        uid                 = "tasktracker";
        password            = "oldmanjohnson";
        connectionString    = "SERVER=" + server + ";" + 
                                "DATABASE=" + database + ";" + 
                                "UID=" + uid + ";" + 
                                "PASSWORD=" + password + ";";
    }

    private bool OpenConnection()
    {
        try
        {
            connection = new MySqlConnection(connectionString);
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            connection.Close();
            switch (ex.Number)
            {
                case 0:
                    break;

                case 1045:
                    break;
            }
            return false;
        }
    }

    public bool CloseConnection()
    {
        try
        {
            connection.Close();
            return true;
        }
        catch (MySqlException ex)
        {
            Console.WriteLine(ex.ToString());
            return false;
        }
    }

    public void Insert(String query)
    {
        if (OpenConnection())
        {
            var cmd = new MySqlCommand(query, connection);

            cmd.ExecuteNonQuery();

            CloseConnection();
        }
    }

    public void Update(String query)
    {
        if (OpenConnection())
        {
            var cmd = new MySqlCommand();
            cmd.CommandText = query;
            cmd.Connection = connection;

            cmd.ExecuteNonQuery();

            CloseConnection();
        }
    }

    public void Delete(String query)
    {
       if (OpenConnection())
        {
            var cmd = new MySqlCommand(query, connection);
            cmd.ExecuteNonQuery();
            CloseConnection();
        }
    }

    /// <summary> Standart select method for database access. 
    /// Always remember to call closeConnection() when finished with reader, to prevent connection locking.</summary>
    public MySqlDataReader Select(String query)
    {
        //Open connection
        if (OpenConnection())
        {
            //Create Command
            var cmd = new MySqlCommand(query, connection);
            //Create a data reader and Execute the command
            MySqlDataReader dataReader = cmd.ExecuteReader();

            //return list to be displayed
            return dataReader;
        }
        else
        {
            return null;
        }
    }

    public String cleanParam(String param)
    {
        param = param.Replace("'", "");
        param = param.Replace("´", "");
        param = param.Replace("--", "");
        param = param.Replace("[", "");
        param = param.Replace("]", "");
        param = param.Replace("%", "");
        param = param.Replace("\\", "");
        
        return param;
    }
}